﻿BEGIN;

-- Create in-memory temp table for variables
PRAGMA temp_store = 2;
DROP TABLE IF EXISTS _Variables;
CREATE TEMP TABLE _Variables(Name TEXT PRIMARY KEY, IntVal INTEGER);

-- Declaring a variable
INSERT INTO _Variables (Name) 
VALUES ('VarName');



INSERT INTO WORDS (WORD)
VALUES ('TEST');



-- Assigning a variable (pick the right storage class)
UPDATE _Variables
SET IntVal = last_insert_rowid() 
WHERE Name = 'VarName';

INSERT INTO WORD_PROPERTIES (WORD_ID , 
							 PRONUNCIATION , 
							 WORD_FORM , 
							 PHONETIC , 
							 PREPOSITION , 
							 VIETNAMESE_MEANING , 
							 ENGLISH_MEANING , 
							 EXAMPLE)
VALUES ( ( SELECT IntVal FROM _Variables WHERE Name = 'VarName' LIMIT 1 ) , 
		 NULL , 
		 NULL , 
		 NULL , 
		 NULL , 
		 NULL , 
		 NULL , 
		 NULL);

INSERT INTO WORD_PROPERTIES (WORD_ID , 
							 PRONUNCIATION , 
							 WORD_FORM , 
							 PHONETIC , 
							 PREPOSITION , 
							 VIETNAMESE_MEANING , 
							 ENGLISH_MEANING , 
							 EXAMPLE)
VALUES ( ( SELECT IntVal FROM _Variables WHERE Name = 'VarName' LIMIT 1 ) , 
		 NULL , 
		 NULL , 
		 NULL , 
		 NULL , 
		 NULL , 
		 NULL , 
		 NULL);

INSERT INTO WORD_PROPERTIES (WORD_ID , 
							 PRONUNCIATION , 
							 WORD_FORM , 
							 PHONETIC , 
							 PREPOSITION , 
							 VIETNAMESE_MEANING , 
							 ENGLISH_MEANING , 
							 EXAMPLE)
VALUES ( ( SELECT IntVal FROM _Variables WHERE Name = 'VarName' LIMIT 1 ) , 
		 NULL , 
		 NULL , 
		 NULL , 
		 NULL , 
		 NULL , 
		 NULL , 
		 NULL);

DROP TABLE _Variables;
		 
END;